﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using MySql.Data.MySqlClient;
using MODEL;
using Common;

namespace DAL
{
    public class UserDAL
    {
        public static async Task<IEnumerable<User>> QueryUsersAsync(UserQueryCriteria criteria = null)
        {
            UserQueryCriteria queryCriteria = criteria ?? new UserQueryCriteria();

            string sql = @"SELECT 
                            A.Id
                            ,A.userId
                            ,B.userName
                            ,A.state
                            ,B.sex
                            ,B.birth
                            ,B.industry
                            ,B.department
                            ,B.telnumber
                            ,B.address
                            ,B.email
                            ,B.mark
                            ,B.accountStatus
                            ,B.province
                            ,B.city
                            ,B.county
                            ,B.addManId
                            ,B.signKey
                            ,B.startTime
                            ,B.endTime
                            FROM msap_db.user A
                            inner join
                            msap_db.user_info B
                            on A.userId = B.userid
                            WHERE
                            (@Industry IS NULL OR B.industry = @Industry)
                            AND(@Province IS NULL OR B.province = @Province)
                            AND(@City IS NULL OR B.city = @City)
                            AND(@County IS NULL OR B.county = @County)
                            AND(@UserId IS NULL OR A.userId = @UserId)
                            AND(@AutoCompleteInput IS NULL OR A.userId LIKE @AutoCompleteInput OR B.userName LIKE @AutoCompleteInput)
                            ORDER BY B.startTime DESC";
            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return await conn.QueryAsync<User>(sql, new {
                    Industry = queryCriteria.Industry,
                    Province = queryCriteria.ProvinceName,
                    City = queryCriteria.CityName,
                    County = queryCriteria.CountyName,
                    UserId = queryCriteria.UserId,
                    AutoCompleteInput = string.IsNullOrEmpty(queryCriteria.AutoCompleteInput) ? null : $"%{queryCriteria.AutoCompleteInput}%"
                });
            }
        }

        public static async Task<IEnumerable<string>> QueryUserIndustryAsync()
        {
            string sql = @"SELECT Distinct industry 
                            FROM msap_db.user_info
                            WHERE industry IS NOT NULL";

            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return await conn.QueryAsync<string>(sql);
            }
        }

        public static async Task<int> AddNewUserAsync(string userId, string password, string userName)
        {
            string sql = @"CALL SP_ADDNewUser(@UserId,@Password,@UserName)";

            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return await conn.ExecuteAsync(sql, new
                {
                    UserId = userId,
                    Password = password?.ToMD5(),
                    UserName = userName
                });
            }
        }

        public static async Task<int> EditUserInfoAsync(User user)
        {
            string sql = @"INSERT INTO msap_db.user_info
                        (
                            userName
                            , sex
                            , birth
                            , industry
                            , department
                            , telNumber
                            , address
                            , email
                            , mark
                            , accountStatus
                            , province
                            , city
                            , county
                            , addManId
                            , signKey
                            , startTime
                        )
                        VALUE
                        (
                            @UserName
                            , @Sex
                            , @Birth
                            , @Industry
                            , @Department
                            , @TelNumber
                            , @Address
                            , @Email
                            , @Mark
                            , @AccountStatus
                            , @Province
                            , @City
                            , @County
                            , @AddManId
                            , @SignKey
                            , now()
                        )";
            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return await conn.ExecuteAsync(sql, new
                {
                    UserName = user.UserName,
                    Sex = user.Sex,
                    Birth = user.Birth,
                    Industry = user.Industry,
                    Department = user.Department,
                    TelNumber = user.TelNumber,
                    Address = user.Address,
                    Email = user.Email,
                    Mark = user.Mark,
                    AccountStatus = user.AccountStatus,
                    Province = user.Province,
                    City = user.City,
                    County = user.County,
                    AddManId = user.AddManId,
                    SignKey = user.SignKey,
                });
            }
        }

        public static async Task<int> UpdateUserState(int userId, string state)
        {
            string sql = @"UPDATE msap_db.user
                            SET state = @State
                            WHERE id = @UserId
                            LIMIT 1";
            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return await conn.ExecuteAsync(sql, new { UserId = userId, State = state });
            }
        }
       
        public static async Task<int> DeleteUser(int userId)
        {
            string sql = @"DELETE FROM 
                            msap_db.user
                             WHERE id = @UserId 
                             LIMIT 1;
 
                            DELETE FROM msap_db.user_role
                            WHERE user_id = @UserId
                             LIMIT 1;";

            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return await conn.ExecuteAsync(sql,new { UserId = userId});
            }
        }

        public static string GetPasswordMD5(string account)
        {
            string sql = @"SELECT 
                            `password` 
                            FROM msap_db.user
                            WHERE userId = @UserId
                            LIMIT 1";
            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return conn.ExecuteScalar<string>(sql, new { UserId = account });
            }
        }
    }
}
